﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using EmoneyWeiXinCommon;
using EmoneyWeiXinEntity;

namespace EmoneyWeiXinDAL
{
    public class StockDoctorlogDAL
    {
        public List<StockDoctorlogContentEntity1> getStockDoctorlogAddALL(string weixinid, string openid, int pageindex, int pagecount)
        {
            int startnum = (pageindex - 1) * pagecount + 1;
            int endnum = pageindex * pagecount;
            string sql = "SELECT * FROM ( ";
            sql += " SELECT t1.FromUserName,t2.nickname,t1.id,t2.headimgurl,T1.wType,t1.isStock,t1.isreply,t1.CONTENT,t1.result,t1.Updatetime,t1.eventkey,t1.PicUrl ,t3.title ";
            sql += " ,ROW_NUMBER() OVER(ORDER BY t1.Updatetime DESC) rownums ";
            sql += " FROM dbo.StockDoctorlog T1 WITH(NOLOCK)  ";
            sql += " INNER JOIN dbo.WXUser t2 WITH(NOLOCK) ";
            sql += " ON t1.FromUserName=t2.openid AND T1.weixinid=t2.weixinUserid ";
            sql += "  LEFT JOIN dbo.menu t3 WITH(NOLOCK) ";
            sql += " ON t1.weixinid=t3.weixinuserid AND t1.eventkey=t3.everyKey";
            sql += " WHERE T1.wType IN(1,10) AND t1.weixinid=" + weixinid + " AND t1.FromUserName='" + openid + "' ";
            sql += " ) T1 WHERE t1.rownums BETWEEN " + startnum + " AND " + endnum + "";
            return SQLHelper.GetDataByColumnsLst<StockDoctorlogContentEntity1>(Config.EmoneyWeiXin, sql, null);
        }

        public string getNickName(string weixinid, string openid)
        {
            string sql = "SELECT TOP 1 nickname FROM dbo.WXUser WITH(NOLOCK) WHERE openid='" + openid + "' AND weixinUserid='" + weixinid + "'";
            return SQLHelper.ExecuteScalar(Config.EmoneyWeiXin, CommandType.Text, sql, null).ToString();
        }


        public int getStockDoctorlogAddNum(string weixinid, string openid)
        {
            string sql = "";
            sql += " SELECT count(1) ";
            sql += " FROM dbo.StockDoctorlog T1 WITH(NOLOCK)  ";
            sql += " INNER JOIN dbo.WXUser t2 WITH(NOLOCK) ";
            sql += " ON t1.FromUserName=t2.openid AND T1.weixinid=t2.weixinUserid ";
            sql += " WHERE T1.wType IN(1,10) AND t1.weixinid=" + weixinid + " AND t1.FromUserName='" + openid + "' ";
            return Convert.ToInt16(SQLHelper.ExecuteScalar(Config.EmoneyWeiXin, CommandType.Text, sql, null));
        }



        public List<StockDoctorlogContentEntity> getStockDoctorlogContentALL(string weixinid, string nickname, string starttime, string endtime, string sex, string sf, string dq, string groupid, int pageindex, int pagecount)
        {
            int startnum = (pageindex - 1) * pagecount + 1;
            int endnum = pageindex * pagecount;

            string sql = "";
            sql += " select * from( SELECT t1.FromUserName,t2.nickname,t1.id,t2.headimgurl,t1.isStock,t1.isreply,t1.CONTENT,t1.result,t1.Updatetime,ROW_NUMBER() OVER(ORDER BY t1.Updatetime DESC) rownum ";
            sql += " FROM dbo.StockDoctorlog T1 WITH(NOLOCK)  ";
            sql += " INNER JOIN dbo.WXUser T2 WITH(NOLOCK) ";
            sql += " ON t1.FromUserName=t2.openid AND t1.weixinid=t2.weixinUserid ";
            sql += " WHERE weixinid=" + weixinid + " AND wType=1 AND ISNULL(CONTENT,'')<>''  ";
            if (!string.IsNullOrEmpty(nickname))
            {
                sql += " AND t2.nickname like '%" + nickname + "%' ";
            }
            if (!string.IsNullOrEmpty(starttime))
            {
                sql += " AND t2.subscribe_time>='" + StringTools.ConvertDateTimeInt(Convert.ToDateTime(starttime)) + "' ";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sql += " AND t2.subscribe_time<='" + StringTools.ConvertDateTimeInt(Convert.ToDateTime(endtime)) + "' ";
            }
            if (!string.IsNullOrEmpty(sex))
            {
                sql += " and t2.sex='" + sex + "'";
            }
            if (!string.IsNullOrEmpty(sf))
            {
                sql += " and t2.province='" + sf + "'";
            }
            if (!string.IsNullOrEmpty(dq))
            {
                sql += " and t2.city='" + dq + "'";
            }
            if (!string.IsNullOrEmpty(groupid))
            {
                sql += " and t2.groupid in(" + groupid + ")";
            }
            sql += " AND not EXISTS( ";
            sql += " SELECT keyword FROM dbo.KeyWordManager WITH(NOLOCK) WHERE isvalid=1 AND keyword=t1.CONTENT ";
            sql += " AND weixinid=t1.weixinid ";
            sql += " ) ";
            sql += " ) table1 where table1.rownum between " + startnum + " and " + endnum;
            return SQLHelper.GetDataByColumnsLst<StockDoctorlogContentEntity>(Config.EmoneyWeiXin, sql, null);
        }


        public int getStockDoctorlogContentCount(string weixinid, string nickname, string starttime, string endtime, string sex, string sf, string dq, string groupid)
        {
            string sql = "";
            sql += " SELECT count(1) ";
            sql += " FROM dbo.StockDoctorlog T1 WITH(NOLOCK)  ";
            sql += " INNER JOIN dbo.WXUser T2 WITH(NOLOCK) ";
            sql += " ON t1.FromUserName=t2.openid AND t1.weixinid=t2.weixinUserid ";
            sql += " WHERE weixinid=" + weixinid + " AND wType=1 AND ISNULL(CONTENT,'')<>''  ";
            if (!string.IsNullOrEmpty(nickname))
            {
                sql += " AND t2.nickname like '%" + nickname + "%' ";
            }
            if (!string.IsNullOrEmpty(starttime))
            {
                sql += " AND t2.subscribe_time>='" + StringTools.ConvertDateTimeInt(Convert.ToDateTime(starttime)) + "' ";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sql += " AND t2.subscribe_time<='" + StringTools.ConvertDateTimeInt(Convert.ToDateTime(endtime)) + "' ";
            }
            if (!string.IsNullOrEmpty(sex))
            {
                sql += " and t2.sex='" + sex + "'";
            }
            if (!string.IsNullOrEmpty(sf))
            {
                sql += " and t2.province='" + sf + "'";
            }
            if (!string.IsNullOrEmpty(dq))
            {
                sql += " and t2.city='" + dq + "'";
            }
            if (!string.IsNullOrEmpty(groupid))
            {
                sql += " and t2.groupid in(" + groupid + ")";
            }
            sql += " AND not EXISTS( ";
            sql += " SELECT keyword FROM dbo.KeyWordManager WITH(NOLOCK) WHERE isvalid=1 AND keyword=t1.CONTENT ";
            sql += " AND weixinid=t1.weixinid ";
            sql += " ) ";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(Config.EmoneyWeiXin, CommandType.Text, sql, null));
        }
    }
}
